package ua.kharkov.khpi.tarasenko.bach.dao.mysql.user;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.ServiceRegistryBuilder;

import ua.kharkov.khpi.tarasenko.bach.core.Constant;
import ua.kharkov.khpi.tarasenko.bach.core.db.DBUtils;
import ua.kharkov.khpi.tarasenko.bach.core.db.EShopDataStoreException;
import ua.kharkov.khpi.tarasenko.bach.core.user.UserSettingsBean;
import ua.kharkov.khpi.tarasenko.bach.dao.core.user.UserDAO;
import ua.kharkov.khpi.tarasenko.bach.dao.core.user.UserDTO;

/**
 * DAO for working with table with users in Derby database.
 * 
 * @author oleksandr
 * 
 */
public class MysqlCustomerDAO implements UserDAO {
	private static final Logger log = Logger.getLogger(MysqlCustomerDAO.class);

	// ===========================================================================
	// Tables column's names
	// ===========================================================================
	private static final String COLUMN_CUSTOMER_ID = "CUSTOMER_ID";
	private static final String COLUMN_CUSTOMER_EMAIL = "CUSTOMER_EMAIL";
	private static final String COLUMN_CUSTOMER_PASSWORD = "CUSTOMER_PASSROWD";
	private static final String COLUMN_CUSTOMER_FIRST_NAME = "CUSTOMER_FIRST_NAME";
	private static final String COLUMN_CUSTOMER_LAST_NAME = "CUSTOMER_LAST_NAME";
	private static final String COLUMN_CUSTOMER_PHONE = "CUSTOMER_PHONE";
	private static final String COLUMN_CUSTOMER_REGISTRATION_DATE = "CUSTOMER_REGISTRATION_DATE";
	private static final String COLUMN_CUSTOMER_ROLE = "CUSTOMER_ROLE";

	// ===========================================================================
	// QUERIES
	// ===========================================================================
	private static final String QUERY_ADD_USER = "INSERT INTO "
			+ Constant.TABLE_CUSTOMER + "(" + COLUMN_CUSTOMER_EMAIL + ", "
			+ COLUMN_CUSTOMER_PASSWORD + ", " + COLUMN_CUSTOMER_FIRST_NAME
			+ ", " + COLUMN_CUSTOMER_LAST_NAME + ", " + COLUMN_CUSTOMER_PHONE
			+ ", " + COLUMN_CUSTOMER_REGISTRATION_DATE + ", "
			+ COLUMN_CUSTOMER_ROLE + ")  VALUES(?, ?, ?, ?, ?, ?, ?)";
	private static final String QUERY_FIND_ALL_USERS = "SELECT * FROM "
			+ Constant.TABLE_CUSTOMER;
	private static final String QUERY_FIND_USER_BY_ID = "SELECT * FROM "
			+ Constant.TABLE_CUSTOMER + " WHERE " + COLUMN_CUSTOMER_ID + "= ?";
	private static final String QUERY_FIND_USER_BY_EMAIL = "SELECT * FROM "
			+ Constant.TABLE_CUSTOMER + " WHERE " + COLUMN_CUSTOMER_EMAIL
			+ "= ?";
	private static final String QUERY_UPDATE_USER = "UPDATE "
			+ Constant.TABLE_CUSTOMER + " SET " + COLUMN_CUSTOMER_FIRST_NAME
			+ "=?, " + COLUMN_CUSTOMER_LAST_NAME + "=?, "
			+ COLUMN_CUSTOMER_PHONE + "=? WHERE " + COLUMN_CUSTOMER_ID + "=?";
	private static final String QUERY_UPDATE_USER_ROLE = "UPDATE "
			+ Constant.TABLE_CUSTOMER + " SET " + COLUMN_CUSTOMER_ROLE
			+ "=? WHERE " + COLUMN_CUSTOMER_ID + "=?";

	private Connection conn;

	/**
	 * Creates entity if the DerbyUserDAO.
	 * 
	 * @param conn
	 *            - connection to the database.
	 * @throws EShopDataStoreException
	 *             if connection is null.
	 */
	public MysqlCustomerDAO(Connection conn)
			throws EShopDataStoreException {
		if (conn == null) {
			throw new EShopDataStoreException("Connection is null!");
		}
		this.conn = conn;
	}

	@Override
	public UserDTO findUserById(int id) throws EShopDataStoreException {
		if (log.isTraceEnabled()) {
			log.trace("User's id to find is " + id);
		}
		PreparedStatement ps = null;
		ResultSet rs = null;
		UserDTO user = null;
		try {
			ps = conn.prepareStatement(QUERY_FIND_USER_BY_ID);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if (rs.next()) {
				user = extractUser(rs);
			}
		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during looking for user with id=" + id, e);
		} finally {
			DBUtils.close(rs, ps);
		}
		return user;
	}

	@Override
	public UserDTO findUserByEmail(String email)
			throws EShopDataStoreException {
		if (log.isTraceEnabled()) {
			log.trace("User's email to find is " + email);
		}
		PreparedStatement ps = null;
		ResultSet rs = null;
		UserDTO user = null;
		try {
			ps = conn.prepareStatement(QUERY_FIND_USER_BY_EMAIL);
			ps.setString(1, email);
			rs = ps.executeQuery();
			if (rs.next()) {
				user = extractUser(rs);
			}
		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during looking for user with email="
							+ email, e);
		} finally {
			DBUtils.close(rs, ps);
		}
		return user;
	}

	@Override
	public ArrayList<UserDTO> findAllUsers()
			throws EShopDataStoreException {

//		// HIB TEST
//		// TODO delete after testing
//		log.info("==================Hibernate=================");
//
//		Configuration configuration = new Configuration();
//		configuration.configure("hibernate.cfg.xml");
//		ServiceRegistry serviceRegistry = new ServiceRegistryBuilder()
//				.applySettings(configuration.getProperties())
//				.buildServiceRegistry();
//
//		Session session = new Configuration().configure()
//				.buildSessionFactory(serviceRegistry).openSession();
//		Query query = session.createQuery("from UserSettingsBean");
//		List<UserSettingsBean> usersSetting = (List<UserSettingsBean>) query
//				.list();
//		log.info(usersSetting);
//
//		log.info("==================Hibernate END=================");
//		// ======================END==============================

		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<UserDTO> users = new ArrayList<UserDTO>();
		try {
			ps = conn.prepareStatement(QUERY_FIND_ALL_USERS);
			rs = ps.executeQuery();
			UserDTO user = null;
			while (rs.next()) {
				user = extractUser(rs);
				users.add(user);
			}
			if (log.isDebugEnabled()) {
				log.debug("found " + users.size() + " users.");
			}
		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during looking for all users!", e);
		} finally {
			DBUtils.close(rs, ps);
		}
		return users;
	}

	@Override
	public int update(UserDTO user) throws EShopDataStoreException {
		int userId = user.getId();

		PreparedStatement ps = null;
		if (userId < 1) {
			throw new EShopDataStoreException(
					"Invalid user's id to change!");
		}
		int rowsUpdated = 0;
		try {
			ps = conn.prepareStatement(QUERY_UPDATE_USER);
			ps.setString(1, user.getFirstName());
			ps.setString(2, user.getLastName());
			ps.setString(3, user.getPhone());
			ps.setInt(4, userId);
			rowsUpdated = ps.executeUpdate();
		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during updating user's info, id=" + userId,
					e);
		} finally {
			DBUtils.close(null, ps);
		}
		return rowsUpdated;
	}

	@Override
	public int addUser(UserDTO user) throws EShopDataStoreException {
		if (log.isTraceEnabled()) {
			log.trace("User to add: " + user);
		}
		PreparedStatement ps = null;
		ResultSet rs = null;
		int userId = 0;
		try {
			ps = conn.prepareStatement(QUERY_ADD_USER,
					Statement.RETURN_GENERATED_KEYS);
			ps.setString(1, user.getEmail());
			ps.setString(2, user.getPassword());
			ps.setString(3, user.getFirstName());
			ps.setString(4, user.getLastName());
			ps.setString(5, user.getPhone());
			ps.setTimestamp(6, new java.sql.Timestamp(user
					.getRegistrationDate().getTime()));

			// Position of ROLE in it's declaration is it's id in table
			ps.setInt(7, user.getRole().ordinal());
			int rowsAdded = ps.executeUpdate();
			if (log.isTraceEnabled()) {
				log.trace("added " + rowsAdded + " users");
			}
			rs = ps.getGeneratedKeys();
			if (rs.next()) {
				userId = rs.getInt(1);
			}
			if (log.isDebugEnabled()) {
				log.debug("new user's id: " + userId);
			}
		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during adding new user with info:\n" + user,
					e);
		} finally {
			DBUtils.close(rs, ps);
		}
		return userId;
	}

	@Override
	public int updateUserRole(UserDTO user)
			throws EShopDataStoreException {
		int userId = user.getId();

		PreparedStatement ps = null;
		if (userId < 1) {
			throw new EShopDataStoreException(
					"Invalid user's id to update role!");
		}
		int rowsUpdated = 0;
		try {
			ps = conn.prepareStatement(QUERY_UPDATE_USER_ROLE);
			ps.setInt(1, user.getRole().ordinal());
			ps.setInt(2, userId);
			rowsUpdated = ps.executeUpdate();
		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during updating user's role, id=" + userId,
					e);
		} finally {
			DBUtils.close(null, ps);
		}
		return rowsUpdated;
	}

	/**
	 * Extract user from result set.
	 * 
	 * @param rs
	 *            - result set with data.
	 * @return extracted user.
	 * @throws SQLException
	 *             if error occurred.
	 */
	private UserDTO extractUser(ResultSet rs) throws SQLException {
		UserDTO user = new UserDTO();
		user.setId(rs.getInt(COLUMN_CUSTOMER_ID));
		user.setPassword(rs.getString(COLUMN_CUSTOMER_PASSWORD));
		user.setEmail(rs.getString(COLUMN_CUSTOMER_EMAIL));
		user.setFirstName(rs.getString(COLUMN_CUSTOMER_FIRST_NAME));
		user.setLastName(rs.getString(COLUMN_CUSTOMER_LAST_NAME));
		user.setPhone(rs.getString(COLUMN_CUSTOMER_PHONE));
		java.util.Date utilDate = DBUtils.convertSqlDateToUtilDate(rs
				.getTimestamp(COLUMN_CUSTOMER_REGISTRATION_DATE));
		user.setRegistrationDate(utilDate);
		user.setRole(rs.getInt(COLUMN_CUSTOMER_ROLE));
		return user;
	}

}
